home *** CD-ROM | disk | FTP | other *** search
/ Visual Basic Source Code / Visual Basic Source Code.iso / vbsource / query-1a / modqbe.bas < prev    next >
Encoding:
BASIC Source File  |  1999-10-14  |  4.7 KB  |  163 lines
  1. Attribute VB_Name = "modQBE"
  2. Option Explicit
  3. 'Data Types for SQL
  4. '-----------------------------------------------------------------
  5. Public Enum DataTypes
  6.     dt_text
  7.     dt_bool
  8.     dt_date
  9.     dt_int
  10. End Enum
  11. Public Sub ClearFields(ByVal PassedForm As Form)
  12.  
  13. Dim i%
  14.  
  15.     For i% = 1 To PassedForm.Count - 1
  16.         If TypeName(PassedForm(i%)) = "TextBox" Then
  17.             PassedForm(i%).Text = ""
  18.         End If
  19.         
  20.         If TypeName(PassedForm(i%)) = "ComboBox" Then
  21.             PassedForm(i%).Text = ""
  22.             PassedForm(i%).ListIndex = -1
  23.         End If
  24.         
  25.         If TypeName(PassedForm(i%)) = "MaskEdBox" Then
  26.             If InStr(PassedForm(i%).Tag, "Date") <> 0 Then
  27.                 PassedForm(i%) = " "
  28.                 PassedForm(i%).Mask = ""
  29.                 PassedForm(i%).Mask = "##/##/####"
  30.             End If
  31.             
  32.             
  33.             If InStr(PassedForm(i%).Tag, "Phone") <> 0 Then
  34.                 'PassedForm(i%) = " "
  35.                 PassedForm(i%).Mask = "0"
  36.                 PassedForm(i%).Mask = "(###)###-####"
  37.             End If
  38.             
  39.         End If
  40.         
  41.         If TypeName(PassedForm(i%)) = "RichTextBox" Then
  42.             PassedForm(i%).Text = ""
  43.         End If
  44.         
  45.         If TypeName(PassedForm(i%)) = "CheckBox" Then
  46.             PassedForm(i%).Value = False
  47.         End If
  48.         
  49.         If TypeName(PassedForm(i%)) = "Data" Then
  50.             PassedForm(i%).RecordSource = ""
  51.             PassedForm(i%).Enabled = False
  52.         End If
  53.             
  54.     Next i%
  55. End Sub
  56.  
  57. Public Function DoQuery(psForm As Form, _
  58.                         psTable As String, _
  59.                         dcData As Data, _
  60.                         Optional psSQL As String, _
  61.                         Optional OrderBy As String) As Boolean
  62.  
  63.                         
  64.  
  65.                         
  66.  
  67. Dim i%
  68. Dim mSQL$, bAnd$, bOr$, Sql$
  69.     
  70.     mSQL$ = "Select * From " & psTable & " Where "
  71.     Sql$ = psSQL$
  72.     
  73.     'loop through all the controls and see if any data is in them and then
  74.     ' build SQL statement based on their data field.
  75.     
  76.     For i% = 1 To psForm.Count - 1
  77.         If Sql$ = "" Then
  78.             bAnd$ = ""
  79.             bOr$ = ""
  80.         Else
  81.             bAnd$ = " and "
  82.             bOr$ = " or "
  83.         End If
  84.         
  85.         'Text Box, RichTextBox
  86.         If TypeName(psForm(i%)) = "TextBox" Or TypeName(psForm(i%)) = "RichTextBox" Then
  87.             
  88.             Sql$ = Sql$ + bAnd$ + psForm(i%).DataField + " Like " + SQLValue("*" & psForm(i%).Text & "*", dt_text)
  89.         End If
  90.          
  91.          'ComboBox
  92.          If TypeName(psForm(i%)) = "ComboBox" Then
  93.             If psForm(i%).ListIndex <> -1 Then
  94.                 Sql$ = Sql$ + bAnd$ + psForm(i%).DataField + " = " + SQLValue(psForm(i%).Text, dt_text)
  95.             End If
  96.          End If
  97.          
  98.          'CheckBox
  99.          If TypeName(psForm(i%)) = "CheckBox" Then
  100.             If psForm(i%).Value = 1 Then
  101.                 Sql$ = Sql$ + bAnd$ + psForm(i%).DataField + " = True"
  102.             End If
  103.          End If
  104.          
  105.          'Masked Edit
  106.          If TypeName(psForm(i%)) = "MaskEdBox" Then
  107.             If InStr(psForm(i%).Tag, "dt_date") > 0 Then
  108.                 If IsDate(Format$(psForm(i%), "##/##/####")) Then
  109.                    Sql$ = Sql$ + bAnd$ + psForm(i%).DataField + " Like " + SQLValue(Format(psForm(i%), "&&/&&/&&&&"), dt_date)
  110.                 End If
  111.             Else
  112.             End If
  113.          End If
  114.     Next i%
  115.     ClearFields psForm
  116.     'enable data control - does the work for us!
  117.     dcData.Enabled = True
  118.     
  119.     'check for criteria
  120.     If Sql$ <> "" Then
  121.         If OrderBy <> "" Then
  122.             Sql$ = Sql$ + " Order By " + OrderBy
  123.         End If
  124.         dcData.RecordSource = mSQL$ & Sql$
  125.         dcData.Refresh
  126.     Else
  127.         MsgBox "No Criteria Selected...", vbExclamation
  128.         dcData.Enabled = False
  129.         Exit Function
  130.         DoQuery = False
  131.     End If
  132.     
  133.     If Not dcData.Recordset.EOF And Not dcData.Recordset.BOF Then
  134.         DoQuery = True
  135.     Else
  136.         MsgBox "No Records Found", vbExclamation
  137.         DoQuery = False
  138.     End If
  139.     
  140.     dcData.Enabled = False
  141.     
  142. End Function
  143.  
  144. Public Function SQLValue(FieldData As String, DataType As DataTypes) As Variant
  145.  
  146.         
  147.     Select Case DataType
  148.         Case Is = dt_text
  149.             SQLValue = Chr(34) + Trim(FieldData) + Chr(34)
  150.         Case Is = dt_bool
  151.             SQLValue = "True"
  152.         Case Is = dt_date
  153.              SQLValue = "#" + Trim(FieldData) + "#"
  154.         Case Is = dt_int
  155.             SQLValue = Trim$(FieldData)
  156.         Case Else
  157.             SQLValue = Trim$(FieldData)
  158.     End Select
  159.  
  160.  
  161. End Function
  162.  
  163.